"""
Case Type   : PACKAGE
Case Name   : 查询系统视图pg_stat_activity，定位包体中open游标的语句存储过程正在执行的sql
Create At   : 2024/10/9
Owner       : lonely-dance
Description :
    1.创建表
    2.创建包体中open游标的语句存储过程
    3.调用存储过程
    4.查询系统视图pg_stat_activity
    5.清理环境，删除存储过程
Expect      :
    1.创建表成功
    2.创建存储过程成功
    3.调用存储过程成功
    4.pg_stat_activity显示调用存储过程正在执行的sql
    5.清理环境成功
History     :
"""
import time
import unittest
import os
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.ComThread import ComThread
from yat.test import Node
from yat.test import macro

primary_sh = CommonSH('PrimaryDbUser')

@unittest.skipIf(3 != primary_sh.get_node_num(), '非1+2环境不执行')
class Analysis(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'---{os.path.basename(__file__)} start---')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.standby1_sh = CommonSH('Standby1DbUser')
        self.p_node = Node('PrimaryDbUser')
        self.s1_node = Node('Standby1DbUser')
        self.constant = Constant()

    def test_Analysis(self):
        text = '-----step1:创建表数据 expect:成功-----'
        self.log.info(text)
        create_data = '''drop table if exists big_table;
            CREATE TABLE big_table (column1 INT, column2 VARCHAR(100)); 
            INSERT INTO big_table (column1, column2) SELECT generate_series(1, 10000000),
            'data' || generate_series(1, 10000000);'''
        self.log.info(create_data)
        result = self.primary_sh.execut_db_sql(create_data)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result, '执行失败:' + text)
        self.assertIn('INSERT 0 10000000', result, '执行失败:' + text)

        text = '-----step2:创建包体中定义的存储过程 expect:成功-----'
        self.log.info(text)
        create_pkg = '''
            DROP package IF EXISTS pack_me;
            CREATE OR REPLACE PACKAGE pack_me IS 
                PROCEDURE test_exec_open0013(); 
            END pack_me; 
        '''
        self.log.info(create_pkg)

        create_pkg_body = '''
            CREATE OR REPLACE PACKAGE BODY pack_me IS 
                PROCEDURE test_exec_open0013() IS 
                DECLARE
                    cur SYS_REFCURSOR; 
                    row RECORD; 
                BEGIN 
                    OPEN cur FOR SELECT column1, column2 FROM big_table WHERE column1 = 9909; 
                    LOOP 
                        FETCH cur INTO row; 
                        EXIT WHEN NOT FOUND; 
                        RAISE NOTICE 'id: %, name: %', row.column1, row.column2; 
                    END LOOP; 
                    CLOSE cur; 
                END; 
            END pack_me; 
        '''
        self.log.info(create_pkg_body)
        result1 = self.primary_sh.execut_db_sql(create_pkg)
        result2 = self.primary_sh.execut_db_sql(create_pkg_body)
        self.log.info(result1)
        self.log.info(result2)
        self.assertIn(self.constant.create_package_success_msg, result1, '执行失败:' + text)
        self.assertIn(self.constant.create_package_body_success_msg, result2, '执行失败:' + text)

        text = '-----step3:调用存储过程 expect:成功-----'
        self.log.info(text)
        sql_cmd1 = f'''source {macro.DB_ENV_PATH};
                gsql -d {self.p_node.db_name} -p {self.p_node.db_port} -c "call pack_me.test_exec_open0013();";'''
        self.log.info(sql_cmd1)
        sh_thread1 = ComThread(self.p_node.sh, args=(sql_cmd1,))
        sh_thread1.setDaemon(True)
        sh_thread1.start()

        time.sleep(1)

        text = '-----step4:查询pg_stat_activity expect:pg_stat_activity显示调用存储过程正在执行的sql-----'
        self.log.info(text)
        sql_cmd = f'''select * from pg_stat_activity where datname = '{self.p_node.db_name}' ;'''
        sql_result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertIn('test_exec_open0013()',
                      sql_result, '执行失败:' + text)

        sh_thread1.join(120)

    def tearDown(self):
        text = '-----step5:清理环境 expect:成功-----'
        self.log.info(text)

        sql_cmd = '''
        drop table IF EXISTS big_table; 
        DROP package IF EXISTS pack_me;
        '''
        self.log.info(sql_cmd)

        sql_result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertTrue(self.constant.drop_package_success_msg in sql_result
                        and self.constant.DROP_TABLE_SUCCESS in sql_result, '执行失败:' + text)



